﻿CREATE TRIGGER [ti_VideoFile]
    ON [dbo].[VideoFile]
    FOR INSERT 
    AS 
    BEGIN
    	SET NOCOUNT ON

		INSERT INTO Song
		SELECT	newid(),
				LTRIM(RTRIM(SUBSTRING(i.OriginalFileName,
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName) + 1
						ELSE 0
					END,
					LEN(REPLACE(i.OriginalFileName,'.' + i.FileType,'')) - 
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName)
						ELSE -1
					END)))
		FROM	inserted i
		WHERE	LTRIM(RTRIM(SUBSTRING(i.OriginalFileName,
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName) + 1
						ELSE 0
					END,
					LEN(REPLACE(i.OriginalFileName,'.' + i.FileType,'')) - 
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName)
						ELSE -1
					END))) NOT IN (
					SELECT	Name
					FROM	Song
				)

		INSERT INTO Artist
		SELECT	newid(),
				LTRIM(RTRIM(SUBSTRING(i.OriginalFileName,0,
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName)
						ELSE LEN(REPLACE(i.OriginalFileName,'.' + i.FileType,'')) + 1
					END)))
		FROM	inserted i
		WHERE	LTRIM(RTRIM(SUBSTRING(i.OriginalFileName,0,
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName)
						ELSE LEN(REPLACE(i.OriginalFileName,'.' + i.FileType,'')) + 1
					END))) NOT IN (
					SELECT	Name
					FROM	Artist
				)
				
		INSERT INTO Video (VideoID,VideoFileID,SongID,StartPosition,Length)
		SELECT	newid(),
				i.VideoFileID,
				s.SongID,
				'0:0',
				i.VideoLength
		FROM	inserted i LEFT OUTER JOIN
				Song s ON s.Name = LTRIM(RTRIM(SUBSTRING(i.OriginalFileName,
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName) + 1
						ELSE 0
					END,
					LEN(REPLACE(i.OriginalFileName,'.' + i.FileType,'')) - 
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName)
						ELSE -1
					END)))

		INSERT INTO Video_Artist (VideoID,ArtistID)
		SELECT	v.VideoID,
				a.ArtistID
		FROM	inserted i INNER JOIN
				Video v ON i.VideoFileID = v.VideoFileID LEFT OUTER JOIN
				Artist a ON a.Name = LTRIM(RTRIM(SUBSTRING(i.OriginalFileName,0,
					CASE 
						WHEN CHARINDEX('-',i.OriginalFileName) > 0 THEN CHARINDEX('-',i.OriginalFileName)
						ELSE LEN(REPLACE(i.OriginalFileName,'.' + i.FileType,'')) + 1
					END)))
    	
    END
